--Declare @tmpDate datetime
--SET @tmpDate = '1-Jan-2014'

--WHILE @tmpDate <= '31-Oct-2014'
--BEGIN
--	SET @tmpDate = DateAdd(month,1, @tmpDate)
--	Select @tmpDate

--	DECLARE curUser CURSOR FOR 
--	Select distinct UserID from tblUser_Log
--	Where Convert(varchar(6), UpdateDate, 112) = Convert(varchar(6), @tmpDate, 112)

--	OPEN curUser

--	Declare @UserID int, @TeamID int
--	FETCH NEXT FROM curUser
--	INTO @UserID

--	WHILE @@FETCH_STATUS = 0
--	BEGIN
		
--		Select top 1 @TeamID = TeamID from tblUser_Log
--		Where UserID = @UserID
--		And Convert(varchar(6), UpdateDate, 112) = Convert(varchar(6), @tmpDate, 112)
--		Order By UpdateDate DESC

--		Select top 1 * FROM tblUser_Log
--		Where UserID = @UserID
--		And Convert(varchar(6), UpdateDate, 112) = Convert(varchar(6), @tmpDate, 112)
--		Order By UpdateDate DESC

--		Update CTI SET CTI.ClosedByTeamID = @TeamID  
--		FROM tblCaseTaskIndex CTI
--		INNER JOIN tblCaseProfileIndex CPI ON CTI.CaseRefID = CPI.CaseRefID And CTI.CaseSourceID = CPI.CaseSourceID
--		Where Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @tmpDate, 112)
--		And CPI.ClosedBy = @UserID 

--		Update CTI SET CTI.ProcessedByTeamID = @TeamID  
--		FROM tblCaseTaskIndex CTI
--		INNER JOIN tblCaseProfileIndex CPI ON CTI.CaseRefID = CPI.CaseRefID And CTI.CaseSourceID = CPI.CaseSourceID
--		Where Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @tmpDate, 112)
--		And CPI.ProcessedBy = @UserID 

--		FETCH NEXT FROM curUser
--		INTO @UserID
--	END

--	CLOSE curUser
--	DEALLOCATE curUser
--END



select top 10 * from tblAskACRA

select UL.UserAccount, UL.DisplayName, TM.TeamName, Count(UHisID) from tblUser_Log UL
INNER JOIN tblTeam TM ON UL.TeamID = TM.TeamID
Where TM.TeamTypeID = 2 
Group By UL.UserAccount, UL.DisplayName, TM.TeamName
Having Count(UL.UHisID) > 1
Order By UL.UserAccount

select * from tblEmailClosedByDivision

select EC.DisplayName, U.UserID from tblEmailClosedByDivision EC
INNER JOIN tblUser U ON EC.DisplayName = U.DisplayName

select EC.TeamName,T.TeamID from tblEmailClosedByDivision EC
INNER JOIN tblTeam T ON EC.TeamName = T.TeamName

--Update EC SET EC.UserID = U.UserID 
--from tblEmailClosedByDivision EC
--INNER JOIN tblUser U ON EC.DisplayName = U.DisplayName

--Update EC SET EC.TeamID = T.TeamID 
--from tblEmailClosedByDivision EC
--INNER JOIN tblTeam T ON EC.TeamName = T.TeamName

select count(*) from tblCaseTaskIndex CTI
INNER JOIN tblCaseProfileIndex CPI ON CTI.CaseRefID = CPI.CaseRefID And CTI.CaseSourceID = CPI.CaseSourceID
INNER JOIN tblEmailClosedByDivision EC ON CPI.ProcessedBy = EC.UserID
INNER JOIN tblEmailClosedByDivision EC2 ON CPI.ClosedBy = EC2.UserID
Where Convert(varchar(6), CPI.ClosedOn, 112) = '201405' And Convert(varchar(6), EC.MonthFor, 112) = '201405'
And Convert(varchar(6), EC2.MonthFor, 112) = '201405'
And EC2.TeamID IN (1,2)
And CTI.CaseSourceID = 1

select count(*) from tblCaseTaskIndex CTI
INNER JOIN tblCaseProfileIndex CPI ON CTI.CaseRefID = CPI.CaseRefID And CTI.CaseSourceID = CPI.CaseSourceID
INNER JOIN tblEmailClosedByDivision EC ON CPI.ProcessedBy = EC.UserID
INNER JOIN tblEmailClosedByDivision EC2 ON CPI.ClosedBy = EC2.UserID
Where Convert(varchar(6), CPI.ClosedOn, 112) = '201405' And Convert(varchar(6), EC.MonthFor, 112) = '201405'
And Convert(varchar(6), EC2.MonthFor, 112) = '201405'
And EC.TeamID = 2 And EC2.TeamID = 2
And CTI.CaseSourceID = 2

--Update CTI SET CTI.ClosedByTeamID = EC.TeamID
--FROM tblCaseTaskIndex CTI
--INNER JOIN tblCaseProfileIndex CPI ON CTI.CaseRefID = CPI.CaseRefID And CTI.CaseSourceID = CPI.CaseSourceID
--INNER JOIN tblEmailClosedByDivision EC ON CPI.ClosedBy = EC.UserID
--Where Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), EC.MonthFor, 112)